package com.dsz.duty.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.dsz.duty.model.User;

/**
 * @author crjun
 */
@Repository
public class UserDao extends JdbcDaoSupport implements IUserDao {

	@Override
	public List<User> getPageUsers(String filter, int page, int rows) {
		return (List<User>) getJdbcTemplate().query(getSql(page, rows, filter),
				new Object[] {}, new UserRowMapper());
	}

	private static final String ADD_USER = "INSERT INTO DutyUser(USERNAME, FULLNAME, PASSWORD, ROLE, LASTLOGINTIME) VALUES(?, ?, ?, ?, ?)";

	@Override
	public void addUser(User user) {
		getJdbcTemplate().update(
				ADD_USER,
				new Object[] { user.getUsername(), user.getFullName(),
						user.getPassword(), user.getRole(), new Date() });
	}

	private static final String REMOVE_USER = "DELETE FROM DutyUser WHERE ID = ?";

	@Override
	public void removeUser(int id) {
		getJdbcTemplate().update(REMOVE_USER, new Object[] { id });
	}

	private static final String UPDATE_USER = "UPDATE DutyUser SET USERNAME = ?, FULLNAME = ?, PASSWORD = ?, ROLE = ?, LASTLOGINTIME = ? WHERE ID = ?";

	@Override
	public void updateUser(User user) {
		getJdbcTemplate().update(
				UPDATE_USER,
				new Object[] { user.getUsername(), user.getFullName(),
						user.getPassword(), user.getRole(), new Date(),
						user.getId() });
	}

	private static final String GET_USERS_COUNT = "SELECT COUNT(*) FROM DutyUser";

	@Override
	public int getUsersCount(String filter) {
		if (null == filter || filter.trim().equals(""))
			return getJdbcTemplate().queryForInt(GET_USERS_COUNT);
		return getJdbcTemplate().queryForInt(GET_USERS_COUNT + " WHERE " + filter);
	}

	@Resource(name = "dataSource")
	public void setSuperDataSource(DataSource dataSource) {
		super.setDataSource(dataSource);
	}

	private static final String GET_USER = "SELECT USERNAME, FULLNAME, PASSWORD, ROLE, LASTLOGINTIME FROM DutyUser WHERE ID = ?";

	@Override
	public User getUser(int id) {
		List<User> users = (List<User>) getJdbcTemplate().query(GET_USER,
				new Object[] { id }, new UserRowMapper());
		if (null != users && !users.isEmpty()) {
			return users.get(0);
		}
		return null;
	}

	private static final String USER_EXISTS = "SELECT COUNT(*) FROM DutyUser WHERE USERNAME = ?";

	@Override
	public boolean userExists(String username) {
		int count = getJdbcTemplate().queryForInt(USER_EXISTS,
				new Object[] { username });
		if (count > 0) {
			return true;
		}
		return false;
	}

	private static final String AUTH = "SELECT COUNT(*) FROM DutyUser WHERE USERNAME = ? AND PASSWORD = ?";
	public boolean auth(String username, String password) {
		int count = getJdbcTemplate().queryForInt(AUTH,
				new Object[] { username, password });
		if (count > 0) {
			return true;
		}
		return false;
	}

	private static final String SET_USER_ROLES = "UPDATE DutyUser SET ROLE = ? WHERE id = ?";

	@Override
	public void setUserRoles(int[] ids, int roleType) {
		getJdbcTemplate().batchUpdate(SET_USER_ROLES,
				new UserBatchPreparedStatementSetter(ids, roleType));
	}

	/* key=value&key='value'&key like %value%&key like '%value%' */
	private String getSql(int page, int rows, String filter) {
		String sql = null;
		if (null == filter || filter.trim().equals("")) {
			sql = "select top "
					+ rows
					+ " ID, USERNAME, FULLNAME, PASSWORD, ROLE, LASTLOGINTIME from DutyUser where ID not in(select top "
					+ (page - 1) * rows
					+ " ID from DutyUser  order by ID) order by ID";

		} else
			sql = "select top "
					+ rows
					+ " ID, USERNAME, FULLNAME, PASSWORD, ROLE, LASTLOGINTIME from DutyUser where ID not in(select top "
					+ (page - 1) * rows + " ID from DutyUser  where " + filter
					+ "  order by ID) and " + filter + " order by ID";
		return sql;
	}

	public static class UserRowMapper implements RowMapper<User> {

		@Override
		public User mapRow(ResultSet rs, int i) throws SQLException {
			User user = new User();
			user.setId(rs.getInt("ID"));
			user.setUsername(rs.getString("USERNAME"));
			user.setFullName(rs.getString("FULLNAME"));
			user.setPassword(rs.getString("PASSWORD"));
			user.setRole(rs.getInt("ROLE"));
			return user;
		}

	}

	public static class UserBatchPreparedStatementSetter implements
			BatchPreparedStatementSetter {

		private int[] userIds;
		private int roleType;

		public UserBatchPreparedStatementSetter(int[] userIds, int roleType) {
			this.userIds = userIds;
			this.roleType = roleType;
		}

		@Override
		public int getBatchSize() {
			return userIds.length;
		}

		@Override
		public void setValues(PreparedStatement pst, int i) throws SQLException {
			pst.setInt(1, roleType);
			pst.setInt(2, userIds[i]);
		}

	}
}
